#!/bin/bash
if [ -n "$2" ] ;then
do_date=$2
else 
do_date=`date -d "-1 day" +%F`
fi
dws_user_action_daycount="
with
tmp_login as
(
select
 tenantdomain,
 uid,
 count(*) login_count
from dwd_page_log
where uid is not null
and last_page_id ='login'
and dt='$do_date'
and substr(timestr,0,10) ='$do_date'
group by tenantdomain,uid
),
tmp_action as
(
select
tenantdomain,
uid,
sum(if(action_id='firstPage',1,0)) firstPage,
sum(if(action_id='myOrders',1,0)) myOrders,
sum(if(action_id='seveiceList',1,0)) seveiceList
from dwd_action_log
where uid is not null
and action_id in ('firstPage','myOrders','seveiceList')
and dt='$do_date'
and substr(timestr,0,10) = ='$do_date'
group by tenantdomain, uid
),
tmp_order as
(
select
domain,
uid,
count(*) order_count,
sum(original_amount) order_original_amount,
sum(final_amount) order_final_amount
from dwd_t_order
where dt='$do_date'
and date_format(created_time,'yyyy-MM-dd')='$do_date'
group by domain,uid
)
insert overwrite table dws_user_action_daycount partition(dt='$do_date')
select
coalesce(tmp_login.tenantdomain,tmp_action.tenantdomain,tmp_order.domain) domain,
coalesce(tmp_login.uid,tmp_action.uid,tmp_order.uid),
nvl(login_count,0),
nvl(firstPage,0),
nvl(myOrders,0),
nvl(seveiceList,0),
nvl(order_count,0),
nvl(order_original_amount,0),
nvl(order_final_amount,0)
from tmp_login
full outer join tmp_action
on tmp_login.uid=tmp_action.uid
and tmp_login.tenantdomain=tmp_action.tenantdomain
full outer join tmp_order
on coalesce(tmp_login.uid,tmp_action.uid)=tmp_order.uid
and coalesce(tmp_login.tenantdomain,tmp_action.tenantdomain)=tmp_order.domain;
"
dws_sku_action_daycount="
with
tmp_order as
(
select
domain,
sku_id,
count(*) order_count,
sum(amount) sku_num,
sum(original_amount) order_original_amount,
sum(final_amount) order_final_amount
from dwd_t_order
where dt='$do_date'
and date_format(created_time,'yyyy-MM-dd')='$do_date'
group by domain,sku_id
)
insert overwrite table dws_sku_action_daycount partition(dt='$do_date')
select
domain,
sku_id,
order_count,
sku_num,
order_original_amount,
order_final_amount
from tmp_order;
"


case $1 in
"dws_user_action_daycount" )
hive -e "$dws_user_action_daycount"
;;
"dws_sku_action_daycount" )
hive -e "$dws_sku_action_daycount"
;;
"all" )
hive -e "$dws_user_action_daycount$dws_sku_action_daycount"
;;
esac
